<?php
/**
 * --------------------------------------------------------------------
 * MCore精简框架
 * 作者：阿枫，QQ：52037872，Email：support@mdeve.com
 * --------------------------------------------------------------------
 *
 * SVN revision information:
 * @version $Revision: 1 $:
 * @author  $Author: zy $:
 * @date      $Date: 2017-03-10 21:12:21 +0800 (星期五, 10 三月 2017) $:
 */

class _DbClass extends __basic_def{

    // 当前连接ID
    protected $_linkID            =   null;
    // 当前查询ID
    protected $queryID          = null;
    // 当前选择的服务器
    protected $linkNum 		= 1;
    // 数据库表达式
    protected $comparison      = array('eq'=>'=','neq'=>'!=','gt'=>'>','egt'=>'>=','lt'=>'<','elt'=>'<=','notlike'=>'NOT LIKE','like'=>'LIKE');
    // 查询表达式
    protected $selectSql = 'SELECT%DISTINCT% %FIELDS% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%';
    // 最后使用的SQL
    protected $queryStr;
    // 是否已经连接数据库
    protected $connected       = false;
    // 数据库存参数
    protected $config       = array();
    // SQL查询次数
    protected $query_number	= 0;
    // 当前查询返回的行数
    protected  $numRows = 0;
    // 最后一次插入记录ID
    protected $lastInsID;

    // 构造函数，初始化数据库连接
    function __construct($n=1){
        global $config;
        $n = intval($n); if(!$n) $n = 1;
        $this->linkNum = $n;
        $this->config = $config["db"][$n];
        $this->connect();
    }

    function __destruct() {
        $this->_linkID = null;
    }

    function connect(){
        if(empty($this->config)) halt("Database is empty!");
        $host = $this->config['dbhost'] . ($this->config['dbport'] ? ";port={$this->config['dbport']}" : '');
        $options = array( PDO::ATTR_PERSISTENT => $this->config['pconnect']);
        if($this->config["ssl"]) {
            // 如果需要证书
            $options[PDO::MYSQL_ATTR_SSL_KEY] = $this->config["client_key"];
            $options[PDO::MYSQL_ATTR_SSL_CERT] = $this->config["client_cert"];
            $options[PDO::MYSQL_ATTR_SSL_CA] = $this->config["ca_cert"];
        }
        try {
            $this->_linkID = new PDO("mysql:host={$host};dbname=". $this->config['dbname'], $this->config['dbuser'], $this->config['dbpwd'], $options);
        }catch (PDOException $e) {
            print "Error!: " . $e->getMessage() . "<br/>";
            die();
        }
        // 获取服务器信息
        //$dbServerInfo = $this->_linkID->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
        // 获取服务器版本
        $dbVersion = $this->_linkID->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));

        if ($dbVersion < "5.0") {
            halt ( "mysql version must 5.0 or later!" );
        }
        // 设置 sql_model
        $this->_linkID->exec( "
				SET CHARACTER_SET_CLIENT = " . $this->config["dbcharset"] . ",
				CHARACTER_SET_CONNECTION = " . $this->config["dbcharset"] . ",
				CHARACTER_SET_DATABASE = " . $this->config["dbcharset"] . ",
				CHARACTER_SET_RESULTS = " . $this->config["dbcharset"] . ",
				CHARACTER_SET_SERVER = " . $this->config["dbcharset"] . ",
				COLLATION_CONNECTION = " . $this->config["dbcharset"] . "_general_ci,
				COLLATION_DATABASE = " . $this->config["dbcharset"] . "_general_ci,
				COLLATION_SERVER = " . $this->config["dbcharset"] . "_general_ci,
				AUTOCOMMIT=0" );
        // 标记连接成功
        $this->connected = true;
    }


    /**
     * 获取一行
     * @param array $options
     * @param boolean $is_func 是否查询的是自定义函数，如果是则会将SQL做特殊处理
     */
    public function get_row($options=array(), $is_func=false){
        $result = $this->select($options, $is_func);
        if(is_array($result) && isset($result[0]))
            $result = $result[0];
        else
            $result = false;
        return $result;
    }

    // select 的别名
    public function get_rows($option=array()){
        return $this->select($option);
    }

    /**
     * 插入记录
     * @access public
     * @param mixed $data 数据
     * @param array $options 参数表达式
     * @param boolean $replace 是否replace，如果是用replace into语法，只有mysql支持
     * @return false | integer
     */
    public function insert($data,$options=array(),$replace=false) {
        $fields = array();
        $values = array();
        foreach ($data as $key=>$val){
            $value   =  $this->parseValue($val);
            if(is_scalar($value)) { // 过滤非标量数据
                if(strtolower($this->dbType) == "mysql"){
                    // 如果数据库是mysql，则将 true 和 false 替换成 1 和 0
                    if($value === "true" || $value === "'true'") $value = 1;
                    if($value === "false" || $value === "'false'") $value = 0;
                    $values[]   =  $value;
                }else{
                    $values[]   =  $value;
                }
                $fields[]     =  $this->addSpecialChar($key);
            }
        }

        $sql   =  ($replace?'REPLACE':'INSERT').' INTO '.$this->parseTable($options['table']).' ('.implode(',', $fields).') VALUES ('.implode(',', $values).')';
        $sql   .= $this->parseLock(isset($options['lock'])?$options['lock']:false);
        $result = $this->execute($sql);
        $this->queryStr = $sql;
        return $result;
    }

    public function replace($data,$options=array()){
        $row = $this->get_row($options);
        if(false === $row) {
            $this->insert($data, $options);
        } else {
            $this->update($data, $options);
        }
    }

    /**
     * 更新记录
     * @access public
     * @param mixed $data 数据
     * @param array $options 表达式
     * @return false | integer
     */
    public function update($data,$options) {
        $sql   = 'UPDATE '
            .$this->parseTable($options['table'])
            .$this->parseSet($data)
            .$this->parseWhere(isset($options['where'])?$options['where']:'')
            .$this->parseOrder(isset($options['order'])?$options['order']:'')
            .$this->parseLimit(isset($options['limit'])?$options['limit']:'')
            .$this->parseLock(isset($options['lock'])?$options['lock']:false);
        $result = $this->execute($sql);
        $this->queryStr = $sql;
        return $result;
    }

    /**
     * 删除记录
     * @access public
     * @param array $options 表达式
     * @return false | integer
     */
    public function delete($options=array())	{
        $sql   = 'DELETE FROM '
            .$this->parseTable($options['table'])
            .$this->parseWhere(isset($options['where'])?$options['where']:'')
            .$this->parseOrder(isset($options['order'])?$options['order']:'')
            .$this->parseLimit(isset($options['limit'])?$options['limit']:'')
            .$this->parseLock(isset($options['lock'])?$options['lock']:false);
        $result = $this->execute($sql);
        $this->queryStr = $sql;
        return $result;
    }

    /**
     * 查找记录
     * @access public
     * @param array $options 表达式
     * @param boolean $is_func 是否查询的是自定义函数，如果是则会将SQL做特殊处理
     * @return array
     */
    public function select($options=array(), $is_func=false) {
        if(isset($options['page'])) {
            // 根据页数计算limit
            list($page,$listRows) =  explode(',',$options['page']);
            $page    = $page ? $page : 1;
            $listRows = $listRows?$listRows:($options['limit']?$options['limit']:20);
            $offset  =  $listRows*((int)$page-1);
            $options['limit'] =  $offset.','.$listRows;
        }
        // 如果是mysql，并且直接是读取数据库函数，则做特殊处理
        if($is_func && strtolower($this->dbType) == "mysql"){
            $sql = "select ". $this->parseTable($options['table']);
        }else{
            $sql   = str_replace(
                array('%TABLE%','%DISTINCT%','%FIELDS%','%JOIN%','%WHERE%','%GROUP%','%HAVING%','%ORDER%','%LIMIT%'),
                array(
                    $this->parseTable($options['table']),
                    $this->parseDistinct(isset($options['distinct'])?$options['distinct']:false),
                    $this->parseField(isset($options['field'])?$options['field']:'*'),
                    $this->parseJoin(isset($options['join'])?$options['join']:''),
                    $this->parseWhere(isset($options['where'])?$options['where']:''),
                    $this->parseGroup(isset($options['group'])?$options['group']:''),
                    $this->parseHaving(isset($options['having'])?$options['having']:''),
                    $this->parseOrder(isset($options['order'])?$options['order']:''),
                    $this->parseLimit(isset($options['limit'])?$options['limit']:'')
                ),
                $this->selectSql);
            $sql   .= $this->parseLock(isset($options['lock'])?$options['lock']:false);
        }
        //echo $sql, "<br>";
        $this->queryStr = $sql;
        $result = $this->query($sql);
        return $result;
    }


    /**
     * 字段和表名添加`
     * 保证指令中使用关键字不出错 针对mysql
     * @access protected
     * @param mixed $value
     * @return mixed
     */
    protected function addSpecialChar(&$value) {
        $value   =  trim($value);
        if( false !== strpos($value,' ') || false !== strpos($value,',') || false !== strpos($value,'*') ||  false !== strpos($value,'(') || false !== strpos($value,'.') || false !== strpos($value,'`')) {
            //如果包含* 或者 使用了sql方法 则不作处理
        }else{
            $value = '`'.$value.'`';
        }
        return $value;
    }

    /**
     * table分析
     * @access protected
     * @param mixed $table
     * @return string
     */
    protected function parseTable($tables) {
        // 如果表名中有括号，表示调用的存储过程，只在最前面增加前缀
        $tablepre = $this->config["tablepre"];
        if(strpos($tables, "(") !== false) return $tablepre. $tables;
        if(is_string($tables)) $tables  =  explode(',',$tables);
        for($i=0; $i<sizeof($tables); $i++){
            if(substr($tables[$i], 0, strlen($tablepre)) != $tablepre){
                // 判断表名中是否有 . 符号，如果有，表示表名包含了库名或模式名，则不增加表前缀
                $tables[$i] = (stripos($tables[$i], ".")===false ? $tablepre : "") . $tables[$i];
            }
        }
        array_walk($tables, array(&$this, 'addSpecialChar'));
        return implode(',',$tables);
    }

    /**
     * distinct分析
     * @access protected
     * @param mixed $distinct
     * @return string
     */
    protected function parseDistinct($distinct) {
        return !empty($distinct)?   ' DISTINCT ' :'';
    }

    /**
     * field分析
     * @access protected
     * @param mixed $fields
     * @return string
     */
    protected function parseField($fields) {
        if(is_array($fields)) {
            // 完善数组方式传字段名的支持
            // 支持 'field1'=>'field2' 这样的字段别名定义
            $array   =  array();
            foreach ($fields as $key=>$field){
                if(!is_numeric($key))
                    $array[] =  $this->addSpecialChar($key).' AS '.$this->addSpecialChar($field);
                else
                    $array[] =  $this->addSpecialChar($field);
            }
            $fieldsStr = implode(',', $array);
        }elseif(is_string($fields) && !empty($fields)) {
            $fieldsStr = $this->addSpecialChar($fields);
        }else{
            $fieldsStr = '*';
        }
        return $fieldsStr;
    }

    /**
     * join分析
     * @access protected
     * @param mixed $join
     * @return string
     */
    protected function parseJoin($join) {
        $joinStr = '';
        if(!empty($join)) {
            if(is_array($join)) {
                foreach ($join as $key=>$_join){
                    if(false !== stripos($_join,'JOIN'))
                        $joinStr .= ' '.$_join;
                    else
                        $joinStr .= ' LEFT JOIN ' .$_join;
                }
            }else{
                $joinStr .= ' LEFT JOIN ' .$join;
            }
        }
        return $joinStr;
    }

    /**
     * where分析
     * @access protected
     * @param mixed $where
     * @return string
     */
    protected function parseWhere($where) {
        $whereStr = '';
        if(is_string($where)) {
            // 直接使用字符串条件
            $whereStr = $where;
        }else{ // 使用数组条件表达式
            if(array_key_exists('_logic',$where)) {
                // 定义逻辑运算规则 例如 OR XOR AND NOT
                $operate    =   ' '.strtoupper($where['_logic']).' ';
                unset($where['_logic']);
            }else{
                // 默认进行 AND 运算
                $operate    =   ' AND ';
            }
            foreach ($where as $key=>$val){
                $whereStr .= "( ";
                if(0===strpos($key,'_')) {
                    // 解析特殊条件表达式
                    $whereStr   .= $this->parseThinkWhere($key,$val);
                }else{
                    $key = $this->addSpecialChar($key);
                    if(is_array($val)) {
                        if(is_string($val[0])) {
                            if(preg_match('/^(EQ|NEQ|GT|EGT|LT|ELT|NOTLIKE|LIKE)$/i',$val[0])) { // 比较运算
                                $whereStr .= $key.' '.$this->comparison[strtolower($val[0])].' '.$this->parseValue($val[1]);
                            }elseif('exp'==strtolower($val[0])){ // 使用表达式
                                $whereStr .= ' ('.$key.' '.$val[1].') ';
                            }elseif(preg_match('/IN/i',$val[0])){ // IN 运算
                                if(is_string($val[1])) {
                                    $val[1] =  explode(',',$val[1]);
                                }
                                $zone   =   implode(',',$this->parseValue($val[1]));
                                $whereStr .= $key.' '.strtoupper($val[0]).' ('.$zone.')';
                            }elseif(preg_match('/BETWEEN/i',$val[0])){ // BETWEEN运算
                                $data = is_string($val[1])? explode(',',$val[1]):$val[1];
                                $whereStr .=  ' ('.$key.' BETWEEN '.$data[0].' AND '.$data[1].' )';
                            }else{
                                halt('error:'.$val[0]);
                            }
                        }else {
                            $count = count($val);
                            if(in_array(strtoupper(trim($val[$count-1])),array('AND','OR','XOR'))) {
                                $rule = strtoupper(trim($val[$count-1]));
                                $count   =  $count -1;
                            }else{
                                $rule = 'AND';
                            }
                            for($i=0;$i<$count;$i++) {
                                $data = is_array($val[$i])?$val[$i][1]:$val[$i];
                                if('exp'==strtolower($val[$i][0])) {
                                    $whereStr .= '('.$key.' '.$data.') '.$rule.' ';
                                }else{
                                    $op = is_array($val[$i])?$this->comparison[strtolower($val[$i][0])]:'=';
                                    $whereStr .= '('.$key.' '.$op.' '.$this->parseValue($data).') '.$rule.' ';
                                }
                            }
                            $whereStr = substr($whereStr,0,-4);
                        }
                    }else {
                        //对字符串类型字段采用模糊匹配
                        if($this->config["system"]["DB_LIKE_FIELDS"] && preg_match('/('.$this->config["system"]["DB_LIKE_FIELDS"].')/i',$key)) {
                            $val  =  '%'.$val.'%';
                            $whereStr .= $key." LIKE ".$this->parseValue($val);
                        }else {
                            $whereStr .= $key." = ".$this->parseValue($val);
                        }
                    }
                }
                $whereStr .= ' )'.$operate;
            }
            $whereStr = substr($whereStr,0,-strlen($operate));
        }
        return empty($whereStr)?'':' WHERE '.$whereStr;
    }

    /**
     * value分析
     * @access protected
     * @param mixed $value
     * @return string
     */
    protected function parseValue($value) {
        if(is_string($value)) {
            $value = '\''.$this->escape_string($value).'\'';
        }elseif(isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp'){
            $value   =  $this->escape_string($value[1]);
        }elseif(is_array($value)) {
            $value   =  array_map(array($this, 'parseValue'),$value);
        }elseif(is_null($value)){
            $value   =  'null';
        }
        return $value;
    }


    /**
     * group分析
     * @access protected
     * @param mixed $group
     * @return string
     */
    protected function parseGroup($group){
        return !empty($group)? ' GROUP BY '.$group:'';
    }

    /**
     * having分析
     * @access protected
     * @param string $having
     * @return string
     */
    protected function parseHaving($having){
        return  !empty($having)?   ' HAVING '.$having:'';
    }


    /**
     * order分析
     * @access protected
     * @param mixed $order
     * @return string
     */
    protected function parseOrder($order) {
        if(is_array($order)) {
            $array   =  array();
            foreach ($order as $key=>$val){
                if(is_numeric($key)) {
                    $array[] =  $this->addSpecialChar($val);
                }else{
                    $array[] =  $this->addSpecialChar($key).' '.$val;
                }
            }
            $order   =  implode(',',$array);
        }
        return !empty($order)?  ' ORDER BY '.$order:'';
    }

    /**
     * limit分析
     * @access protected
     * @param mixed $lmit
     * @return string
     */
    protected function parseLimit($limit) {
        return !empty($limit)?   ' LIMIT '.$limit.' ':'';
    }

    /**
     * set分析
     * @access protected
     * @param array $data
     * @return string
     */
    protected function parseSet($data) {
        foreach ($data as $key=>$val){
            $value   =  $this->parseValue($val);
            if(is_scalar($value)) // 过滤非标量数据
                $set[]    = $this->addSpecialChar($key).'='.$value;
        }
        return ' SET '.implode(',',$set);
    }


    /**
     * 设置锁机制
     * @access protected
     * @return string
     */
    protected function parseLock($lock=false) {
        if(!$lock) return '';
        if('oracle' == $this->dbType) {
            return ' FOR UPDATE NOWAIT ';
        }
        return ' FOR UPDATE ';
    }









    /**
     * 释放查询结果
     *
     * @access public
     */
    public function free() {
        $this->queryID = null;
    }


    /**
     * 执行查询 返回数据集
     *
     * @access public
     * @param string $sql
     *        	sql指令
     * @return mixed
     */
    public function query($sql) {
        if (!$this->_linkID)	return false;
        $this->queryStr = $sql;
        // 释放前次的查询结果
        if ($this->queryID) {
            $this->free ();
        }
        // 记录开始执行时间
        $this->queryID = $this->_linkID->query($sql);
        if (false === $this->queryID) {
            $this->error ();
            return false;
        } else {
            $this->numRows = $this->queryID->rowCount();
            return $this->getAll ();
        }
    }


    /**
     * 执行语句
     *
     * @access public
     * @param string $sql
     *        	sql指令
     * @return integer
     */
    public function execute($sql) {
        if (! $this->_linkID)
            return false;
        $this->queryStr = $sql;
        // 释放前次的查询结果
        if ($this->queryID) {
            $this->free ();
        }
        //echo $sql, "<br>";
        // N('db_write',1);
        // 记录开始执行时间
        // G('queryStartTime');
        $result = $this->_linkID->exec ( $sql );
        $this->numRows = $result;
        $this->lastInsID = $this->_linkID->lastInsertId();
        return $result;
    }

    /**
     * 启动事务
     *
     * @access public
     * @return void
     */
    public function startTrans() {
        if (! $this->_linkID)
            return false;
        // 数据rollback 支持
        if ($this->transTimes == 0) {
            $this->_linkID->beginTransaction();
        }
        $this->transTimes ++;
        return;
    }

    /**
     * 用于非自动提交状态下面的查询提交
     *
     * @access public
     * @return boolen
     */
    public function commit() {
        if ($this->transTimes > 0) {
            $this->_linkID->commit();
        }
        return true;
    }

    /**
     * 事务回滚
     *
     * @access public
     * @return boolen
     */
    public function rollback() {
        if ($this->transTimes > 0) {
            $this->_linkID->rollBack();
        }
        return true;
    }

    /**
     * 获得所有的查询数据
     *
     * @access public
     * @return array
     */
    public function getAll() {
        // 返回数据集
        $result = array ();
        if ($this->numRows > 0) {
            $result = $this->queryID->fetchAll(PDO::FETCH_ASSOC);
        }
        return $result;
    }

    /**
     * 取得数据表的字段信息
     *
     * @access public
     */
    public function getFields($tableName) {
        $result = $this->query("select column_name,data_type,column_type,character_maximum_length,column_comment,is_nullable,column_default,extra
				from information_schema.columns where table_name='". str_replace("`", "", $this->parseTable($tableName))  . "' and table_schema='" . $this->config["dbname"] . "'");
        $info = array ();
        if ($result) {
            foreach ( $result as $key => $val ) {
                $info [$val['column_name']] = array (
                    'name' => $val['column_name'],
                    'type' => $val['data_type'],
                    'column_type' => $val['column_type'],
                    'null' => $val["is_nullable"]="YES" ? true : false,
                    'default' => $val['column_default'],
                    'primary' => "",
                    'autoinc' => $val['extra'],
                    'comment' => $val['column_comment'],
                    'max_length' => $val['character_maximum_length'],
                );
            }
        }
        return $info;
    }

    /**
     * 取得数据库的表信息
     *
     * @access public
     */
    public function getTables($dbName = '') {
        if (! empty ( $dbName )) {
            $sql = 'SHOW TABLES FROM ' . $dbName;
        } else {
            $sql = 'SHOW TABLES ';
        }
        $result = $this->_linkID->query ( $sql );
        $info = array ();
        foreach ( $result as $key => $val ) {
            $info [$key] = current ( $val );
        }
        return $info;
    }


    /**
     * 插入记录
     *
     * @access public
     * @param mixed $datas
     *        	数据
     * @param array $options
     *        	参数表达式
     * @return false | integer
     */
    public function insertAll($datas, $options = array()) {
        if (! is_array ( $datas [0] ))
            return false;
        $fields = array_keys ( $datas [0] );
        array_walk ( $fields, array (
            $this,
            'addSpecialChar'
        ) );
        $values = array ();
        foreach ( $datas as $data ) {
            $value = array ();
            foreach ( $data as $key => $val ) {
                $val = $this->parseValue ( $val );
                if (is_scalar ( $val )) { // 过滤非标量数据
                    $value [] = $val;
                }
            }
            $values [] = '(' . implode ( ',', $value ) . ')';
        }
        $sql = 'INSERT INTO ' . $this->parseTable ( $options ['table'] ) . ' (' . implode ( ',', $fields ) . ') VALUES ' . implode ( ',', $values );
        return $this->execute ( $sql );
    }

    /**
     * 数据库错误信息
     * 并显示当前的SQL语句
     *
     * @access public
     * @return string
     */
    public function error() {
        if($this->_linkID){
            $this->error = $this->_linkID->errorInfo();
        }else{
            $this->error = "link error!";
        }
        return $this->error;
    }

    /**
     * SQL指令安全过滤
     *
     * @access public
     * @param string $sql
     *        	SQL字符串
     * @return string
     */
    public function escape_string($sql) {
        return addslashes ( $sql );
    }

    /**
     * @deprecated
     * @return integer
     */
    function insert_id(){
        return $this->lastInsID;
    }

    /**
     * @return integer
     */
    function get_insert_id() {
        return $this->lastInsID;
    }

    /**
     * @return mixed
     *
     * 获取最后执行的sql
     */
    public function get_last_sql() {
        return $this->queryStr;
    }

    /**
     * @deprecated
     * @return string
     */
    public function getLastSql(){
        return $this->queryStr;
    }

}